<?php
/**
 * Created by PhpStorm.
 * User: wangjiangtao
 * Date: 2021/11/6
 * Time: 17:50
 */

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class StatisticRoom extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'statisticRoom';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '厅日统计';

    protected $manageCon = 'mysql';
    protected $apiCon = 'api';
    protected $statisticCon = 'statistic';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        set_time_limit(0);

        $yesterdayDate = date('Y-m-d', strtotime("-1 days"));
        $yesterdayStart = strtotime($yesterdayDate.' 00:00:00');
        $yesterdayEnd = strtotime($yesterdayDate.' 23:59:59');

        $data = [];
        // $statRoom = new StatRoom();
        //todo 总进厅人数
        $sql = "SELECT count(*) joinNum,room_id FROM zb_room_join_log WHERE date = '".$yesterdayDate."' GROUP BY room_id";
        $joinRoomData = DB::connection($this->apiCon)->select($sql);
        $joinRoomData = objToArray($joinRoomData);
        if($joinRoomData) {
            foreach ($joinRoomData as $v) {
                $insertSql = "INSERT INTO kk_stat_room(room_id, `statis_time`, joinNum) VALUES(".$v['room_id'].", '".$yesterdayDate." 00:00:00', ".$v['joinNum'].") ON DUPLICATE KEY UPDATE `joinNum`=".$v['joinNum'];
                Db::connection($this->statisticCon)->insert($insertSql, []);
            }
        }

        //todo 进入萌新数量
        $threeDayBeforeYesterRQ = strtotime(date("Y-m-d 00:00:00",strtotime("-3 days",strtotime($yesterdayDate))));
        $mengxinSql = "SELECT count(*) mengxin, room_id FROM zb_room_join_log jlog LEFT JOIN zb_user_register_log rlog ON jlog.user_id = rlog.user_id WHERE `date` = '{$yesterdayDate}' AND `time` BETWEEN {$threeDayBeforeYesterRQ} AND {$yesterdayEnd} GROUP BY room_id";
        $mengxinData = DB::connection($this->apiCon)->select($mengxinSql);
        $mengxinData = objToArray($mengxinData);
        if($mengxinData) {
            foreach ($mengxinData as $v) {
                $insertSql = "INSERT INTO kk_stat_room(room_id, `statis_time`, mengxin) VALUES({$v['room_id']}, '{$yesterdayDate} 00:00:00', {$v['mengxin']}) ON DUPLICATE KEY UPDATE `mengxin`={$v['mengxin']}";
                Db::connection($this->statisticCon)->insert($insertSql, []);
            }
        }

        //todo 进入萌新消耗额
        $threeDayBeforeYesterRQ = strtotime(date("Y-m-d 00:00:00",strtotime("-3 days",strtotime($yesterdayDate))));
        $mengxinConsumeSql = "SELECT count(DISTINCT jlog.user_id) mengxin_consume_num, jlog.room_id ,sum(wealth_value) mengxin_wealth_value  FROM zb_room_join_log jlog left JOIN zb_user_register_log rlog ON jlog.user_id = rlog.user_id inner join zb_finance_room_wealth_stream slog on slog.user_id = jlog.user_id and slog.room_id = jlog.room_id  WHERE `date` = '{$yesterdayDate}' AND `time` BETWEEN {$threeDayBeforeYesterRQ} AND {$yesterdayEnd} And create_timestamp BETWEEN {$yesterdayStart} AND {$yesterdayEnd} AND wealth_value > 0 GROUP BY jlog.room_id";
        $mengxinConsumeData = DB::connection($this->apiCon)->select($mengxinConsumeSql);
        $mengxinConsumeData = objToArray($mengxinConsumeData);
        if($mengxinConsumeData) {
            foreach ($mengxinConsumeData as $v) {
                $insertSql = "INSERT INTO kk_stat_room(room_id, `statis_time`, mengxin_consume_num,mengxin_wealth_value) VALUES({$v['room_id']}, '{$yesterdayDate} 00:00:00', {$v['mengxin_consume_num']},{$v['mengxin_wealth_value']}) ON DUPLICATE KEY UPDATE `mengxin_consume_num`={$v['mengxin_consume_num']} , `mengxin_wealth_value`={$v['mengxin_wealth_value']}";
                Db::connection($this->statisticCon)->insert($insertSql, []);
            }
        }

        //todo 消费人数
        $consumerNumSql = "SELECT room_id,count(DISTINCT user_id) consumerNum FROM zb_finance_room_wealth_stream WHERE create_timestamp BETWEEN {$yesterdayStart} AND {$yesterdayEnd} GROUP BY room_id";
        $consumerData = DB::connection($this->apiCon)->select($consumerNumSql);
        $consumerData = objToArray($consumerData);
        if($consumerData) {
            foreach ($consumerData as $v) {
                $insertSql = "INSERT INTO kk_stat_room(room_id, `statis_time`, consumerNum) VALUES({$v['room_id']}, '{$yesterdayDate} 00:00:00', {$v['consumerNum']}) ON DUPLICATE KEY UPDATE `consumerNum`={$v['consumerNum']}";
                Db::connection($this->statisticCon)->insert($insertSql, []);
            }
        }

        //todo 日流水
        $dayofStearmSql = "SELECT room_id,(sum(wealth_value) / 100) dayofStream FROM zb_finance_room_wealth_stream WHERE create_timestamp BETWEEN {$yesterdayStart} AND {$yesterdayEnd} GROUP BY room_id";
        $dayofStearmData = DB::connection($this->apiCon)->select($dayofStearmSql);
        $dayofStearmData = objToArray($dayofStearmData);
        if($dayofStearmData) {
            foreach ($dayofStearmData as $v) {
                $insertSql = "INSERT INTO kk_stat_room(room_id, `statis_time`, dayofStream) VALUES({$v['room_id']}, '{$yesterdayDate} 00:00:00', {$v['dayofStream']}) ON DUPLICATE KEY UPDATE `dayofStream`={$v['dayofStream']}";
                Db::connection($this->statisticCon)->insert($insertSql, []);
            }
        }

        //todo 开厅时长
        $openTimeSql = "SELECT room_id,(total_time / 3600) openTime FROM zb_room_open_time WHERE date = {$yesterdayStart}";
        $openTimeData = DB::connection($this->apiCon)->select($openTimeSql);
        $openTimeData = objToArray($openTimeData);
        if($openTimeData) {
            foreach ($openTimeData as $v) {
                $insertSql = "INSERT INTO kk_stat_room(room_id, `statis_time`, openTime) VALUES({$v['room_id']}, '{$yesterdayDate} 00:00:00', {$v['openTime']}) ON DUPLICATE KEY UPDATE `openTime`={$v['openTime']}";
                Db::connection($this->statisticCon)->insert($insertSql, []);
            }
        }

        $now_time = time();
        //todo 厅扣私信人数
        $privateLogSql = "SELECT room_id,un_id,clog.user_id,to_user_id FROM zb_room_chat_private_log clog INNER JOIN zb_union_signing usig on clog.user_id = usig.user_id WHERE UNIX_TIMESTAMP(`created_time`) BETWEEN {$yesterdayStart} AND {$yesterdayEnd} AND  (expire_time = 0 OR expire_time > {$now_time}) AND `date` != ''";
        $privateLogData = DB::connection($this->apiCon)->select($privateLogSql);
        $privateLogData = objToArray($privateLogData);
        $privateLogFastData = [];
        $privateMXNewData = [];

        //获取房间公会数据
        $roomList = array_column($privateLogData,'room_id');
        $unionList = DB::connection($this->apiCon)->table('union_room')->whereIn('room_id',$roomList)->select('room_id','un_id')->get();
        $unionList = objToArray($unionList);
        $unionList = array_column($unionList, 'un_id','room_id');

        $privateLogInfoData = [];
        //先获取到用户是属于哪个公会，在哪个厅扣的私信，然后再判断这个厅是否属于这个公会
        foreach ($privateLogData as $k => $v) {
            if(isset($v['un_id']) && isset($unionList[$v['room_id']]) && $v['un_id'] == $unionList[$v['room_id']]) {
                $privateLogFastData[$v['room_id']] = $privateLogFastData[$v['room_id']] ?? 0;
                $privateLogFastData[$v['room_id']] += 1;
                //私信用户记录
                if (!isset($privateLogInfoData[$v['room_id'].$v['user_id']])){
                    $privateLogInfoData[$v['room_id'].$v['user_id']] = [
                        'room_id' => $v['room_id'],
                        'user_id' => $v['user_id'],
                        'day' => $yesterdayDate,
                        'mengxin_msg_num' => 0,
                        'msg_num' => 1,
                        'un_id' => $v['un_id']
                    ];
                }else{
                    $privateLogInfoData[$v['room_id'].$v['user_id']]['msg_num']++;
                }

                //todo 厅给萌新扣私信人数
                $privateMXSql = "SELECT id FROM zb_user_register_log clog WHERE `time` BETWEEN {$threeDayBeforeYesterRQ} AND {$yesterdayEnd} AND user_id = {$v['to_user_id']}";
                $privateMXData = DB::connection($this->apiCon)->select($privateMXSql);
                $privateMXData = objToArray($privateMXData);
                if($privateMXData) {
                    $privateMXNewData[$v['room_id']] = $privateMXNewData[$v['room_id']] ?? 0;
                    $privateMXNewData[$v['room_id']] += 1;
                    $privateLogInfoData[$v['room_id'].$v['user_id']]['mengxin_msg_num']++;
                }
            }
        }

        if(!empty($privateLogFastData)) {
            foreach ($privateLogFastData as $k => $v) {
                $insertSql = "INSERT INTO kk_stat_room(room_id, `statis_time`, privateNum) VALUES({$k}, '{$yesterdayDate} 00:00:00', {$v}) ON DUPLICATE KEY UPDATE `privateNum`={$v}";
                Db::connection($this->statisticCon)->insert($insertSql, []);
            }

            foreach ($privateLogInfoData as $v) {
                $insertSql = "INSERT INTO kk_stat_room_private_msg(room_id, `day`, mengxin_msg_num,msg_num,user_id,union_id) VALUES({$v['room_id']},'{$v['day']}',{$v['mengxin_msg_num']},{$v['msg_num']},{$v['user_id']},{$v['un_id']}) ON DUPLICATE KEY UPDATE `mengxin_msg_num`={$v['mengxin_msg_num']},`msg_num` ={$v['msg_num']} ";
                Db::connection($this->statisticCon)->insert($insertSql, []);
            }
        }
        if(!empty($privateMXNewData)) {
            foreach ($privateMXNewData as $k => $v) {
                $insertSql = "INSERT INTO kk_stat_room(room_id, `statis_time`, privateMXNum) VALUES({$k}, '{$yesterdayDate} 00:00:00', {$v}) ON DUPLICATE KEY UPDATE `privateMXNum`={$v}";
                Db::connection($this->statisticCon)->insert($insertSql, []);
            }
        }

        //todo 厅公屏聊天人数
        $publicChatSql = "SELECT to_user_id, count(DISTINCT user_id) publicChat FROM cms_chatpublic_log WHERE dateTime BETWEEN {$yesterdayStart} AND {$yesterdayEnd} GROUP BY to_user_id;";
        $publicChatData = DB::connection($this->manageCon)->select($publicChatSql);
        $publicChatData = objToArray($publicChatData);
        if($publicChatData) {
            foreach ($publicChatData as $v) {
                $insertSql = "INSERT INTO kk_stat_room(room_id, `statis_time`, publicChat) VALUES({$v['to_user_id']}, '{$yesterdayDate} 00:00:00', {$v['publicChat']}) ON DUPLICATE KEY UPDATE `publicChat`={$v['publicChat']}";
                Db::connection($this->statisticCon)->insert($insertSql, []);
            }
        }
        $this->send_data("{$yesterdayDate} 00:00:00");
    }
    public function send_data($date){
        $roomSql = "SELECT room_id FROM zb_room WHERE business_type=1 AND certified=1";
        $room_ids = DB::connection($this->apiCon)->select($roomSql);
        $room_ids = objToArray($room_ids);
        $room_ids_str = implode(',', array_column($room_ids,'room_id'));
        $datasql = "SELECT * FROM kk_stat_room WHERE statis_time = '{$date}' AND room_id IN ($room_ids_str)";
        $data = Db::connection($this->statisticCon)->select($datasql);
        $data = objToArray($data);
        foreach ($data as $v) {
            unset($v['id']);

            $url = config('kkaudio.kkaudio_api') . '/analysis/stat/room/insert';
            $res = curl_request($url, $v, 'json', 'POST');
        }
    }


}
